package com.carreirati.evaluation.domain;

import java.util.List;
import org.springframework.roo.addon.dbre.RooDbManaged;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.jpa.activerecord.RooJpaActiveRecord;
import org.springframework.roo.addon.json.RooJson;
import org.springframework.transaction.annotation.Transactional;

@RooJavaBean
@RooJpaActiveRecord(identifierType = AppraisalRelationshipPK.class, versionField = "", table = "appraisal_relationship", schema = "dbo")
@RooDbManaged(automaticallyDelete = true)
@RooJson
public class AppraisalRelationship {

	public static long countAppraisalRelationships() {
        return entityManager().createQuery("SELECT COUNT(*) FROM AppraisalRelationship o", Long.class).getSingleResult();
    }

    public static List<com.carreirati.evaluation.domain.AppraisalRelationship> findAppraisalRelationship(long appraisalId, long questionId) {
        if (appraisalId <= 0) return null;
        return entityManager().createNativeQuery("SELECT * FROM Appraisal_Relationship WHERE appraisal_Id = '" + appraisalId + "' AND question_id = '" + questionId + "'", AppraisalRelationship.class).getResultList();
    }

    public static List<com.carreirati.evaluation.domain.AppraisalRelationship> findAppraisalRelationshipByAppraisalId(Long appraisalId) {
    	String sqlString = "SELECT a.* FROM Appraisal_Relationship a inner join question q ON a.question_id = q.id INNER JOIN "; 
    			sqlString += " knowledge_area ka ON q.knowledge_area_id = ka.id WHERE appraisal_Id = '"+ appraisalId+"'";
    			sqlString += " ORDER BY ka.description,q.description";	
        return entityManager().createNativeQuery(sqlString, AppraisalRelationship.class).getResultList();
    }
    
    public static List<com.carreirati.evaluation.domain.AppraisalRelationship> findAppraisalRelationshipByAppraisalId(Long appraisalId, long knowledgeAreaId) {
    	String sqlString = "SELECT a.* FROM Appraisal_Relationship a inner join question q ON a.question_id = q.id INNER JOIN "; 
    			sqlString += " knowledge_area ka ON q.knowledge_area_id = ka.id WHERE appraisal_Id = '"+ appraisalId+"'";
    			sqlString += " AND q.knowledge_area_id = '" + knowledgeAreaId + "' ";
    			sqlString += " ORDER BY ka.description,q.description";	
        return entityManager().createNativeQuery(sqlString, AppraisalRelationship.class).getResultList();
    }
    
    @Transactional
    public int updateAppraisalRelationship(Long appraisalId, Long questionId, String comment) {
    	 if (this.entityManager == null) this.entityManager = entityManager();
    	 this.entityManager.flush();
         String sqlString = "";
         sqlString = "UPDATE Appraisal_Relationship SET comment = '"+ comment + "' WHERE appraisal_Id = '" + appraisalId + "' AND question_id = '" + questionId + "'";
         
    	return this.entityManager.createNativeQuery(sqlString).executeUpdate();  
    }
    
    @Transactional
    public int addNew(long appraisalId, long questionId) {
    	 if (this.entityManager == null) this.entityManager = entityManager();
    	 this.entityManager.flush();
         String sqlString = "";
         sqlString = "INSERT INTO Appraisal_Relationship (appraisal_id, question_id, is_optional) VALUES ('"+ appraisalId + "','"+ questionId +"','Y')";
        
    	return  this.entityManager.createNativeQuery(sqlString).executeUpdate();

    }
    
    
    @Transactional
    public int removeQuestion(long appraisalId, long questionId) {
    	 if (this.entityManager == null) this.entityManager = entityManager();
    	 this.entityManager.flush();
    	 //delete this question results..
    	 String sqlString = "";
         sqlString = "DELETE FROM Appraisal_Results WHERE appraisal_id ='"+ appraisalId + "' AND question_Id ='"+ questionId +"'";
         this.entityManager.createNativeQuery(sqlString).executeUpdate();
    	 
         sqlString = "";
         sqlString = "DELETE FROM Appraisal_Relationship WHERE appraisal_id ='"+ appraisalId + "' AND question_Id ='"+ questionId +"'";
         
    	return  this.entityManager.createNativeQuery(sqlString).executeUpdate();

    }
	  
   
}
